package com.h5ve.toolkit.util;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.text.CharSequenceUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.h5ve.base.constant.CommonConstants;
import com.h5ve.base.constant.GenConstants;
import com.h5ve.base.util.PinyinUtil;
import com.h5ve.base.util.StrUtil;
import com.h5ve.security.util.SecurityUtil;
import com.h5ve.system.entity.Menu;
import com.h5ve.system.service.MenuService;
import com.h5ve.toolkit.entity.Column;
import com.h5ve.toolkit.entity.Form;
import com.h5ve.toolkit.entity.PageInfo;
import com.h5ve.toolkit.entity.Table;
import lombok.SneakyThrows;
import org.anyline.proxy.ServiceProxy;
import org.anyline.service.AnylineService;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author h5ve
 * @date 2020/4/11
 * @description sql 脚本构成器
 */
public class BuilderUtil {

    /**
     * 根据动态表单生成信息创建表单数据
     *
     * @param form 动态表单生成信息
     * @return 表单数据
     */
    public static Table createForm(Form form) {
        Table table = new Table();
        table.setCode(form.getCode());
        //添加动态表单同意前缀
        table.setTableName("form_" + form.getTableName());
        table.setTableComment(form.getName());
        table.setClassName(StrUtil.upperFirst(form.getTableName()));
        //设置表单的类型
        if ("0".equals(form.getType())) {
            table.setTplCategory(GenConstants.TPL_CRUD);
        } else if ("1".equals(form.getType())) {
            table.setTplCategory(GenConstants.TPL_TREE);
        } else if ("2".equals(form.getType())) {
            table.setTplCategory(GenConstants.TPL_MSUB);
        }
        table.setPackageName("com.h5ve.biz.form");
        table.setModuleName(form.getTableName());
        table.setBusinessName(form.getTableName());
        table.setFunctionName(form.getTableName());
        table.setFunctionAuthor("h5ve");
        table.setChildTable(form.getChildTable());
        //设置父级菜单ID
        table.setMenuId(form.getMenuId());
        //设置树表信息
        if (CharSequenceUtil.isNotEmpty(form.getTreeName())) {
            String options = String.format("{\"treeId\":\"id\",\"treeName\":\"%s\",\"treeParentId\":\"parent_id\"}", form.getTreeName());
            table.setOptions(options);
        }
        table.setCols("12");
        table.setGenApi("0");
        table.setGenWay("0");
        table.setOptions("{}");
        table.setColumns(createColumns(form.getData(), form.getType()));
        return table;
    }

    /**
     * 主子表时创建子表
     *
     * @param form 动态表单详细信息
     * @param tableName 子表名称
     * @return 返回table对象
     */
    public static Table createForm(Form form, String tableName) {
        if (!CharSequenceUtil.isNotEmpty(tableName))
            return null;
        Table table = new Table();
        table.setCode(IdWorker.get32UUID());  //生成新code值
        table.setTableName(tableName.contains(CommonConstants.SQLPREFIX) ? tableName : CommonConstants.SQLPREFIX + tableName);
        table.setTableComment(form.getName());
        table.setChildTable(form.getChildTable());

        table.setClassName(StrUtil.upperFirst(tableName));
        table.setTplCategory("crud");
        table.setPackageName("com.h5ve.biz.form");
        table.setModuleName(tableName);
        table.setBusinessName(tableName);
        table.setFunctionName(tableName);
        table.setFunctionAuthor("h5ve");

        table.setCols("12");
        table.setGenApi("0");
        table.setGenWay("0");
        table.setOptions("{}");

        String str = form.getData();
        if (str.indexOf("\"") != -1) {
            str.replace("\"", "");
        }
        JSONObject jsonObject = JSONUtil.parseObj(str);
        JSONArray jsonArray = jsonObject.getJSONArray("column");
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject jsonObject1 = jsonArray.getJSONObject(i);
            if (jsonObject1.getStr("prop").equals(tableName)) {
                String childColumn = jsonObject1.getJSONObject("plugin").getStr("option");
                table.setColumns(createColumns(childColumn, "0"));
                break;
            }
        }
        return table;
    }

    /**
     * 根据给出的生成动态表单信息生成字段信息
     *
     * @param data 给出生成表单的详细信息
     * @param type 生成表单的类型
     * @return 返回：根据表单信息所生成的动态表单字段信息
     */
    public static List<Column> createColumns(String data, String type) {
        //替换掉表单信息中的所有\"符号
        if (data.indexOf("\"") != -1) {
            data.replace("\"", "");
        }
        JSONObject jsonObject = JSONUtil.parseObj(data);
        JSONArray jsonArray = jsonObject.getJSONArray("column");

        List<Column> columnList = new ArrayList<>();
        if (jsonArray != null && jsonArray.size() > 0) {
            //公共字段id主键
            Column column1 = new Column();
            column1.setColumnName("id");
            column1.setColumnComment("编号");
            column1.setColumnType("int(11)");
            column1.setJavaType("Integer");
            column1.setJavaField("id");
            column1.setIsPk("1");
            column1.setIsIncrement("1");
            column1.setIsList("1");
            column1.setQueryType("eq");
            column1.setHtmlType("input");
            columnList.add(column1);
            // TODO: 2022/1/26  如果为树表 则要加parentId ancestors
            if ("1".equals(type)) {  //树表
                //树表公共字段：父类id
                Column column2 = new Column();
                column2.setColumnName("parent_id");
                column2.setColumnComment("父ID");
                column2.setColumnType("int(11)");
                column2.setJavaType("Integer");
                column2.setJavaField("parentId");
                column2.setQueryType("eq");
                column2.setHtmlType("input");
                column2.setIsAdd("1");
                columnList.add(column2);
                //树表公共字段：父类id集合
                Column column3 = new Column();
                column3.setColumnName("ancestors");
                column3.setColumnComment("父ID集合");
                column3.setColumnType("varchar(100)");
                column3.setJavaType("String");
                column3.setJavaField("ancestors");
                column3.setQueryType("eq");
                column3.setHtmlType("input");
                columnList.add(column3);
            }
            //遍历设置的字段信息
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject1 = jsonArray.getJSONObject(i);
                Column c = new Column();
                c.setColumnName(jsonObject1.getStr("prop"));
                c.setColumnComment(jsonObject1.getStr("label"));
                c.setDictType(jsonObject1.containsKey("dictType") ? jsonObject1.getStr("dictType") : null);
                //根据页面设置的类型设置动态动态表单的字段类型、长度
                switch (jsonObject1.getStr("type")) {
                    case "input":
//                        c.setColumnType("varchar(255)");
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType(jsonObject1.getStr("javaType"));
                        if (CharSequenceUtil.endWithIgnoreCase(jsonObject1.getStr("prop"), "name")) {
                            c.setQueryType(GenConstants.QUERY_LIKE);
                            c.setIsQuery("1");
                        } else {
                            c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        }
                        c.setHtmlType("input");
                        break;
//                    case "inputNumber":
//                        c.setColumnType(getColumnType(jsonObject1));
//                        c.setJavaType(jsonObject1.getStr("javaType"));
//                        if (CharSequenceUtil.endWithIgnoreCase(jsonObject1.getStr("prop"), "name")) {
//                            c.setQueryType(GenConstants.QUERY_LIKE);
//                            c.setIsQuery("1");
//                        } else {
//                            c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
//                        }
//                        c.setHtmlType("input");
//                        break;
                    case "password":
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setIsAdd("1");
                        c.setQueryType("eq");
                        c.setHtmlType("input");
                        break;
                    case "textarea":
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType(jsonObject1.getStr("javaType"));
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("textarea");
                        break;
                    case "number":
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("Integer");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("input");
                        break;
                    case "radio":
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("radio");
                        break;
                    case "checkbox":
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("checkbox");
                        break;
                    case "select":
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("select");
                        break;
                    case "upload":
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("upload");
                        break;
                    case "inputNumber":
                        String queryType = jsonObject1.getStr("plugin");
                        JSONObject jsondata = JSONUtil.parseObj(queryType);
                        c.setColumnType("decimal(" + jsonObject1.getStr("dataLength") + ")");
                        c.setJavaType("BigDecimal");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("upload");
                        c.setDecimals(jsondata.getInt("precision"));
                        break;
                    case "date":
                        JSONObject jsonObject2 = jsonObject1.getJSONObject("plugin");
                        switch (jsonObject2.getStr("type")) {
                            case "date":
                                c.setColumnType("datetime(0)");
                                break;
                            case "year":
                                c.setColumnType("varchar(20)");
                                break;
                            case "month":
                                c.setColumnType("varchar(40)");
                                break;
                            case "dates":
                                c.setColumnType("varchar(500)");
                                break;
                            case "datetime":
                                c.setColumnType("datetime(0)");
                                break;
                            case "datetimerange":
                                c.setColumnType("varchar(200)");
                                break;
                            case "daterange":
                                c.setColumnType("varchar(200)");
                                break;
                            default:
                                c.setColumnType("varchar(120)");
                        }
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("datetime");
                        break;
                    case "time":
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("input");
                        break;
                    case "rate":  //评价
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("rate");
                        break;
                    case "inputColor":  //颜色选择器
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("inputColor");
                        break;
                    case "switch":  //开关
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("switch");
                        break;
                    case "slider":  //滑块
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("slider");
                        break;
                    case "font":  //文字
                        c.setColumnType(getColumnType(jsonObject1));
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("font");
                        break;
                    case "cascader":  //级联选择器
                        c.setColumnType(getColumnType(jsonObject1));   //暂存json
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("cascader");
                        break;
                    case "childForm":  //子表单列  主表结构不保存子表单列
                        c = null;
//                        c.setColumnType(getColumnType(jsonObject1));  //暂时存ID
//                        c.setJavaType("String");
//                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
//                        c.setHtmlType("childForm");
                        break;
                    case "treeSelect":   //树形选择器
                        c.setColumnType(getColumnType(jsonObject1));   //暂存json
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("treeSelect");
                        break;
                    case "user":    //用户
                        c.setColumnType(getColumnType(jsonObject1));   //暂存json
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("user");
                        break;
                    case "institution":   //机构
                        c.setColumnType(getColumnType(jsonObject1));   //暂存json
                        c.setJavaType("String");
                        c.setQueryType(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("queryType")));
                        c.setHtmlType("user");
                        break;
                    default:
                        break;
                }
                if (c != null) {
                    c.setJavaField(CharSequenceUtil.toCamelCase(jsonObject1.getStr("prop")));  //字段名
                    c.setIsAdd(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("isAdd")));
                    c.setIsEdit(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("isEdit")));
                    c.setIsList(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("isList")));
                    c.setIsQuery(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("isQuery")));
                    c.setIsImport(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("isImport")));
                    c.setIsExport(CharSequenceUtil.toUnderlineCase(jsonObject1.getStr("isExport")));
                    c.setEditReadonly(CharSequenceUtil.toUnderlineCase(CharSequenceUtil.isNotEmpty(jsonObject1.getStr("editReadonly")) ? jsonObject1.getStr("editReadonly") : "0"));
                    c.setIsRequired("true".equals(jsonObject1.getJSONObject("validateConfig").getStr("required")) ? "1" : "0");
                    if (!Objects.equals(null, jsonObject1.get("dictType")) || !Objects.equals("", jsonObject1.get("dictType"))) {
                        c.setDictType(jsonObject1.getStr("dictType"));
                    }
                    c.getIsExport();
                    columnList.add(c);
                }
            }
            //动态表单公共字段
            //创建人
            Column column2 = new Column();
            column2.setColumnName("create_by");
            column2.setColumnComment("创建人");
            column2.setColumnType("varchar(60)");
            column2.setJavaType("String");
            column2.setJavaField("createBy");
            column2.setQueryType("eq");
            column2.setHtmlType("input");
            columnList.add(column2);
            //创建时间
            Column column3 = new Column();
            column3.setColumnName("create_time");
            column3.setColumnComment("创建时间");
            column3.setColumnType("datetime");
            column3.setJavaType("Date");
            column3.setJavaField("createTime");
            column3.setIsQuery("0");
            column3.setIsList("0");
            column3.setQueryType("between");
            column3.setHtmlType("datetime");
            columnList.add(column3);
            //更新人
            Column column4 = new Column();
            column4.setColumnName("update_by");
            column4.setColumnComment("更新人");
            column4.setColumnType("varchar(60)");
            column4.setJavaType("String");
            column4.setJavaField("updateBy");
            column4.setQueryType("eq");
            column4.setHtmlType("input");
            //更新时间
            columnList.add(column4);
            Column column5 = new Column();
            column5.setColumnName("update_time");
            column5.setColumnComment("更新时间");
            column5.setColumnType("datetime");
            column5.setJavaType("Date");
            column5.setJavaField("updateTime");
            column5.setQueryType("between");
            column5.setHtmlType("datetime");
            columnList.add(column5);
            Column column6 = new Column();
            column6.setColumnName("remarks");
            column6.setColumnComment("备注");
            column6.setColumnType("varchar(255)");
            column6.setJavaType("String");
            column6.setJavaField("remarks");
            column6.setIsAdd("1");
            column6.setIsEdit("1");
            column6.setQueryType("eq");
            column6.setHtmlType("textarea");
            //删除标识
            columnList.add(column6);
            Column column7 = new Column();
            column7.setColumnName("del_flag");
            column7.setColumnComment("删除标识（0-正常，1-删除）");
            column7.setColumnType("char(1)");
            column7.setJavaType("String");
            column7.setJavaField("delFlag");
            column7.setDefValue("0");
            column7.setQueryType("eq");
            column7.setHtmlType("input");
            columnList.add(column7);
            //数据来源
            Column column8 = new Column();
            column8.setColumnName("data_from");
            column8.setColumnComment("数据来源");
            column8.setColumnType("varchar(3)");
            column8.setJavaType("String");
            column8.setJavaField("dataFrom");
            column8.setDefValue("0");
            column8.setQueryType("eq");
            column8.setHtmlType("input");
            columnList.add(column8);

        }
        return columnList;
    }

    /**
     * 根据生成信息判断是否是时间类型
     *
     * @param jsonObject1 字段信息字符串
     * @return 判断后需要生成的字段类型、长度
     */
    private static String getColumnType(JSONObject jsonObject1) {
//        return jsonObject1.getStr("dataType") + "(" + jsonObject1.getStr("dataLength") + ")";
        return StrUtil.equals(jsonObject1.getStr("dataType"), "date") ? jsonObject1.getStr("dataType") : jsonObject1.getStr("dataType") + "(" + jsonObject1.getStr("dataLength") + ")";
    }

    //创建表
    public static String createTable(Table table) {
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE `" + table.getTableName() + "`  (");
        if (table.getColumns() != null && table.getColumns().size() > 0) {
            for (int i = 0; i < table.getColumns().size(); i++) {
                Column column = table.getColumns().get(i);
                if (i == table.getColumns().size() - 1) {
                    sb.append(fieldCreateScript(column));
                } else {
                    sb.append(fieldCreateScript(column) + ",");
                }
            }
        }
        sb.append(")ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci" + (StrUtil.isNotEmpty(table.getTableComment()) ? " COMMENT '" + table.getTableComment() + "' " : " ") + "ROW_FORMAT = Dynamic;");
        return sb.toString();
    }

    //字段信息转sql脚本(创建)
    private static String fieldCreateScript(Column column) {
        String script = column.getColumnName() + " " + column.getColumnType() + " " + ((("1").equals(column.getIsRequired()) || "1".equals(column.getIsPk())) ? "NOT NULL" : "NULL");
        script += (StrUtil.isNotEmpty(column.getColumnComment()) ? " COMMENT '" + column.getColumnComment() + "'" : " ");
        script += (StrUtil.isNotEmpty(column.getDefValue()) ? " DEFAULT " + column.getDefValue() : " ");
        if ("id".equalsIgnoreCase(column.getColumnName()) && "1".equals(column.getIsPk())) {
            script += " primary key AUTO_INCREMENT";
        }
        return script;
    }

    //更新表
    public static String updateTable(Table table) {
        StringBuilder sb = new StringBuilder();
        if (StrUtil.isNotEmpty(table.getTableName())) {
            sb.append("alter table " + table.getTableName() + " comment '" + table.getTableComment() + "';");
        }
        if (table.getColumns() != null && table.getColumns().size() > 0) {
            for (Column column : table.getColumns()) {
                if (StrUtil.isBlankIfStr(column.getId())) {
                    sb.append("alter table " + table.getTableName() + " add " + column.getColumnName() + " " + column.getColumnType()
                            + (StrUtil.isNotEmpty(column.getDefValue()) ? " default '" + column.getDefValue() + "' " : " ") + (("1").equals(column.getIsRequired()) ? "NOT NULL " : "NULL ") + (StrUtil.isNotEmpty(column.getColumnComment()) ? "comment '" + column.getColumnComment() + "';" : ";"));
                } else {
                    if ("id".equalsIgnoreCase(column.getColumnName()) && "1".equals(column.getIsPk())) {
                        sb.append("alter table " + table.getTableName() + " modify " + column.getColumnName() + " " + column.getColumnType()
                                + (StrUtil.isNotEmpty(column.getDefValue()) ? " default '" + column.getDefValue() + "' " : " ") + (StrUtil.isNotEmpty(column.getColumnComment()) ? "comment '" + column.getColumnComment() + "' auto_increment;" : " auto_increment;"));
                    } else {
                        sb.append("alter table " + table.getTableName() + " modify " + column.getColumnName() + " " + column.getColumnType()
                                + (StrUtil.isNotEmpty(column.getDefValue()) ? " default '" + column.getDefValue() + "' " : " ") + (("1").equals(column.getIsRequired()) ? "NOT NULL " : "NULL ") + (StrUtil.isNotEmpty(column.getColumnComment()) ? "comment '" + column.getColumnComment() + "';" : ";"));
                    }
                }
            }
        }
        return sb.toString();
    }

    /**
     * 更新表
     *
     * @param table   修改后的表数据
     * @param columns 修改前的表字段数据
     * @return
     */
    public static String updateTable(Table table, List<Column> columns) {
        StringBuilder sb = new StringBuilder();
        List<Column> columnList = table.getColumns();

        //新增
        for (Column column : columnList) {
            Boolean add = true;
            for (Column column1 : columns) {
                if (Objects.equals(column.getColumnName(), column1.getColumnName())) {
                    add = false;
                }
            }
            if (add) {     //不存在为新增
                sb.append("alter table " + table.getTableName() + " add " + column.getColumnName()
                        + " " + column.getColumnType() + (CharSequenceUtil.isNotEmpty(column.getDefValue()) ? " default '"
//                        + column.getDefValue() + "' " : " ") + (("1").equals(column.getIsRequired()) ? "NOT NULL " : "NULL ")
                        + column.getDefValue() + "' " : " ") + (CharSequenceUtil.isNotEmpty(column.getColumnComment()) ? "comment '" + column.getColumnComment() + "';" : ";"));
            }
        }
        //删除
        for (Column column : columns) {
            Boolean del = true;
            for (Column column1 : columnList) {
                if (Objects.equals(column.getColumnName(), column1.getColumnName())) {
                    del = false;
                }
            }
            if (del) {     //不存在为删除
                sb.append("alter table " + table.getTableName() + " drop " + column.getColumnName() + "; ");
            }
        }

        //更新
        //更新前的数据
        for (Column column : columns) {
            //更新后的数据
            for (Column column1 : columnList) {
                //判断是更否更改了字段信息
                if (ObjectUtil.equals(column.getColumnName(), column1.getColumnName())) {
                    //判断改变的具体字段并且设置相关修改字段的sql，字段注释，字段名称，字段类型，保留小数位
                    //设置保留小数位数
                    if (!ObjectUtil.equals(column.getColumnComment(), column1.getColumnComment())
                            || !ObjectUtil.equals(column.getColumnType(), column1.getColumnType())
                            || !ObjectUtil.equals(column.getColumnName(), column1.getColumnName())
                            || !ObjectUtil.equals(column.getDecimals(), column1.getDecimals())
                    ) {
                        //设置保留小数位数
                        String columnType = column1.getColumnType();
                        if (!Objects.equals(column1.getDecimals(), null) && !Objects.equals(column1.getDecimals(), "")) {
                            int index = columnType.lastIndexOf(")");
                            columnType = columnType.substring(0, index) + ", " + column1.getDecimals() + ")";
                        }
                        sb.append("alter table " + table.getTableName() + " modify " + column1.getColumnName()
                                + " " + columnType + " comment '" + column1.getColumnComment() + "'; ");
                    }
                }
            }

        }


        return sb.toString();
    }


    //更新表
    public static String updateTableMethod(Table table) {
        StringBuilder sb = new StringBuilder();
        sb.append("CREATE TABLE `" + table.getTableName() + "`  (");
        if (table.getColumns() != null && table.getColumns().size() > 0) {
            for (int i = 0; i < table.getColumns().size(); i++) {
                Column column = table.getColumns().get(i);
                if (i == table.getColumns().size() - 1) {
                    sb.append(fieldCreateScript(column));
                } else {
                    sb.append(fieldCreateScript(column) + ",");
                }
            }
        }
        sb.append(")ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci"
                + (CharSequenceUtil.isNotEmpty(table.getTableComment()) ? " COMMENT '" + table.getTableComment()
                + "' " : " ") + "ROW_FORMAT = Dynamic;");
        return sb.toString();
    }

    //根据sql脚本获得表名
    public static String getTableName(String scripts) {
        String tableName = "";
        if (scripts.indexOf("from") != -1) {
            tableName = scripts.substring(scripts.indexOf("from") + 4);

            if (tableName.indexOf("where") != -1) {
                tableName = tableName.substring(0, tableName.indexOf("where"));
            }

            tableName = tableName.replace(" ", "");
        }
        return tableName;
    }

    /**
     * 根据表名返回所有列字段，用逗号隔开
     *
     * @param table 表对象
     * @param isAll 是否返回全部列  1返回全部列   0返回除（id,create_by,create_time,update_by,update_time,remarks,del_flag）的列
     * @return
     */
    public static String getColumns(Table table, String isAll) {
        if (table != null && CharSequenceUtil.isNotBlank(isAll)) {
            List<Column> columns = table.getColumns();
            if (columns.size() > 0) {
                String columnStr = "";
                for (Column column : columns) {
                    if (!CharSequenceUtil.containsAny(column.getColumnName(), GenConstants.COLUMNNAME_NOT_EDIT) && CharSequenceUtil.equals(column.getIsAdd(), "1") && "0".equals(isAll) || "1".equals(isAll)) {
                        columnStr += column.getColumnName() + ",";
                    }
                }
                return columnStr.substring(0, columnStr.length() - 1);
            }
        }
        return null;
    }

    /**
     * 数据
     */
    public static String getInsertSql(Table table, JSONObject object) {
        String data = object.getStr("data") == null ? "[" + JSONUtil.toJsonStr(object) + "]" : object.getStr("data");

        if (table != null && CharSequenceUtil.isNotBlank(data) && !"{}".equals(data)) {
            StringBuilder sb = new StringBuilder();
            StringBuilder val = new StringBuilder();
            String result;

            sb.append("insert into " + table.getTableName() + " (");

            JSONArray arr = JSONUtil.parseArray(data);
            List<Column> columns = table.getColumns();

            for (Column column : columns) {
                if (!CharSequenceUtil.containsAny(column.getColumnName(), "id", "update_by", "update_time", "remarks", "del_flag"))
                    sb.append(column.getColumnName() + ",");
            }
            result = sb.substring(0, sb.length() - 1) + ") values ";
            for (int i = 0; i < arr.size(); i++) {

                StringBuilder s = new StringBuilder();
                s.append("(");
                JSONObject obj = arr.getJSONObject(i);
                for (Column column : columns) {
                    if (!CharSequenceUtil.containsAny(column.getColumnName(), "id", "update_by", "update_time", "remarks", "del_flag")) {
                        if ("create_by".equals(column.getColumnName())) {
                            s.append("'" + SecurityUtil.getUser().getUserName() + "',");
                        } else if ("create_time".equals(column.getColumnName())) {
                            s.append("'" + DateTime.now() + "',");
                        } else {
                            if (CharSequenceUtil.containsAny(column.getJavaType(), "String", "Char"))
                                s.append("'" + obj.getStr(column.getColumnName()) + "',");
                            else
                                s.append(obj.getStr(column.getColumnName()) + ",");
                        }
                    }
                }
                val.append(s.substring(0, s.length() - 1) + "),");
            }
            result += val.substring(0, val.length() - 1) + ";";
            return result;
        }
        return null;
    }

    /**
     * 获取更新子表sql
     */
    public static String getUpdateSql(Table table, JSONObject object) {
        String data = object.getStr("data");
        String foreignKey = object.getStr("foreignKey");
        String foreignValue = object.getStr("foreignValue");
        if (table != null && CharSequenceUtil.isNotBlank(data) && !"{}".equals(data) && CharSequenceUtil.isNotEmpty(foreignValue)) {
            JSONArray arr = JSONUtil.parseArray(data);
            List<Column> columns = table.getColumns();
            String val = "";

            String ids = "";
            for (int i = 0; i < arr.size(); i++) {
                JSONObject obj = arr.getJSONObject(i);

                if (CharSequenceUtil.isEmpty(obj.getStr("id"))) {  //若ID为空则新增一条
                    JSONArray jsonArray = new JSONArray();
                    jsonArray.add(obj);
                    val += getInsertSql(table, obj);
                } else {
                    ids += obj.getStr("id") + ",";
                    StringBuilder s = new StringBuilder();
                    s.append("update " + table.getTableName() + " set ");

                    for (Column column : columns) {
                        if (!CharSequenceUtil.containsAny(column.getColumnName(), "id", "create_by", "create_time", "remarks", "del_flag")) {
                            if ("update_by".equals(column.getColumnName())) {
                                s.append("update_by='" + SecurityUtil.getUser().getUserName() + "',");
                            } else if ("update_time".equals(column.getColumnName())) {
                                s.append("update_time='" + DateTime.now() + "',");
                            } else {
                                if (CharSequenceUtil.containsAny(column.getJavaType(), "String", "Char"))
                                    s.append(column.getColumnName() + "='" + obj.getStr(column.getColumnName()) + "',");
                                else
                                    s.append(column.getColumnName() + "=" + obj.getStr(column.getColumnName()) + ",");
                            }
                        }
                    }
                    val += (s.substring(0, s.length() - 1) + " where id=" + obj.getStr("id")) + ";";
                }
            }
            //设置删除的数据
            if (CharSequenceUtil.isNotEmpty(ids)) {
                val = "update " + table.getTableName() + " set del_flag='1' where " + foreignKey + "=" + foreignValue + " and del_flag='0' and id not in (" + ids.substring(0, ids.length() - 1) + ") ;" + val;
            } else {
                val = "update " + table.getTableName() + " set del_flag='1' where " + foreignKey + "=" + foreignValue + " and del_flag='0' ;" + val;
            }
            return val;
        }
        return null;
    }

    @SneakyThrows
    public static void createTable(PageInfo pageInfo) {
        AnylineService service = ServiceProxy.service();
        // 如果已存在，删除重键
        org.anyline.metadata.Table table = service.metadata().table(CommonConstants.SQLPREFIX + pageInfo.getTableName(), false);
        if (null != table) {
            service.ddl().drop(table);
        }else{
            table = new org.anyline.metadata.Table(CommonConstants.SQLPREFIX + pageInfo.getTableName());
        }
        //表字段
        JSONObject columnList = JSONUtil.parseObj(pageInfo.getPageData());
        JSONArray jsonArray = columnList.getJSONArray("list");
        table.addColumn("id", "bigint(20)").setAutoIncrement(true).setPrimaryKey(true).setComment("序号");
        for (int i = 0; i < jsonArray.size(); i++) {
            JSONObject columnObj = jsonArray.getJSONObject(i);
            JSONObject options = columnObj.getJSONObject("options");
            String type = options.getStr("fieldType");
            int fieldLength = options.getInt("fieldLength");
            if(fieldLength != 0){
                type += "(" + fieldLength + ")";
            }
            String dvalue = options.getStr("defaultValue");
            if(StrUtil.isNotBlank(dvalue)){
                table.addColumn(columnObj.getStr("model"), type).setDefaultValue(dvalue);
            }else {
                table.addColumn(columnObj.getStr("model"), type);
            }
        }
        //默认字段
        table.addColumn("dept_id", "bigint(20)").setComment("部门编号");
        table.addColumn("create_by", "varchar(60)").setComment("创建人");
        table.addColumn("create_time", "datetime").setComment("创建时间");
        table.addColumn("update_by", "varchar(60)").setComment("更新人");
        table.addColumn("update_time", "datetime").setComment("更新时间");
        table.addColumn("remarks", "varchar(255)").setComment("备注");
        table.addColumn("del_flag", "char(1)").setDefaultValue('0').setComment("删除标识（0-正常，1-删除）");
        // 执行建表SQL
        service.ddl().create(table);
    }

    /**
     * 创建菜单数据
     */
    public static void createMenu(String name, long applicationId, String remarks) {
        MenuService menuService = SpringUtil.getBean(MenuService.class);
        //新增菜单数据
        Menu menu = menuService.getOne(new LambdaQueryWrapper<Menu>().eq(Menu::getName, name).eq(Menu::getApplicationId, applicationId));
        if (menu == null) {
            menu = new Menu();
            menu.setParentId(0l);
            menu.setName(name);
            String enname = PinyinUtil.getPinYin(name);
            menu.setEnname(enname);
            menu.setApplicationId(applicationId);
            menu.setType("M");
            menu.setPath(enname);
            menu.setIcon("app");
            menu.setNoCache("0");
            menu.setSort(1);
            menu.setStatus("0");
            menu.setRemarks(remarks);
            menuService.save(menu);
        } else {
            menu.setRemarks(remarks);
            menuService.updateById(menu);
        }
    }

    /**
     * 创建菜单
     */
    public static void createAllMenu(PageInfo pageInfo) {
        MenuService menuService = SpringUtil.getBean(MenuService.class);
        //新增菜单数据
        Menu menu = menuService.getOne(new QueryWrapper<Menu>().eq("parent_id", pageInfo.getMenuId()).eq("path", "generate/" + pageInfo.getCode()));
        if (menu == null) {
            menu = new Menu();
            menu.setParentId(pageInfo.getMenuId());
            menu.setName(pageInfo.getName());
            //menu.setApplicationId(1);
            menu.setType("C");
            menu.setPath("generate/" + pageInfo.getCode());
            menu.setComponent("toolkit/pageInfo/generate");
            menu.setIcon("star");
            menu.setNoCache("0");
            menu.setSort(1);
            menu.setStatus("0");
            menuService.save(menu);
            //新增菜单中资源数据
            JSONArray jsonArray = JSONUtil.parseArray("[{\"name\":\"新增\",\"permissions\":\"add\"},{\"name\":\"修改\",\"permissions\":\"edit\"},{\"name\":\"删除\",\"permissions\":\"del\"},{\"name\":\"导入\",\"permissions\":\"import\"},{\"name\":\"导出\",\"permissions\":\"export\"}]");
            if (jsonArray != null && jsonArray.size() > 0) {
                for (int i = 0; i < jsonArray.size(); i++) {
                    JSONObject jsonObject = jsonArray.getJSONObject(i);
                    Menu menu1 = new Menu();
                    switch (jsonObject.getStr("name")) {
                        case "新增":
                            menu1 = menuService.getOne(new QueryWrapper<Menu>().eq("parent_id", menu.getId()).eq("perms", pageInfo.getCode() + "_add"));
                            if (menu1 == null) {
                                menu1 = new Menu();
                                menu1.setPerms(pageInfo.getTableName() + "_add");
                            }
                            break;
                        case "搜索":
                            menu1 = menuService.getOne(new QueryWrapper<Menu>().eq("parent_id", menu.getId()).eq("perms", pageInfo.getCode() + "_view"));
                            if (menu1 == null) {
                                menu1 = new Menu();
                                menu1.setPerms(pageInfo.getTableName() + "_view");
                            }
                            break;
                        case "修改":
                            menu1 = menuService.getOne(new QueryWrapper<Menu>().eq("parent_id", menu.getId()).eq("perms", pageInfo.getCode() + "_edit"));
                            if (menu1 == null) {
                                menu1 = new Menu();
                                menu1.setPerms(pageInfo.getTableName() + "_edit");
                            }
                            break;
                        case "删除":
                            menu1 = menuService.getOne(new QueryWrapper<Menu>().eq("parent_id", menu.getId()).eq("perms", pageInfo.getCode() + "_del"));
                            if (menu1 == null) {
                                menu1 = new Menu();
                                menu1.setPerms(pageInfo.getTableName() + "_del");
                            }
                            break;
                        case "导出":
                            menu1 = menuService.getOne(new QueryWrapper<Menu>().eq("parent_id", menu.getId()).eq("perms", pageInfo.getCode() + "_export"));
                            if (menu1 == null) {
                                menu1 = new Menu();
                                menu1.setPerms(pageInfo.getTableName() + "_export");
                            }
                            break;
                        default:
                            menu1 = menuService.getOne(new QueryWrapper<Menu>().eq("parent_id", menu.getId()).eq("perms", pageInfo.getCode() + "_" + jsonObject.getStr("permissions")));
                            if (menu1 == null) {
                                menu1 = new Menu();
                                menu1.setPerms(pageInfo.getTableName() + "_" + jsonObject.getStr("permissions"));
                            }
                            break;
                    }
                    menu1.setParentId(menu.getId());
                    menu1.setName(pageInfo.getName() + jsonObject.getStr("name"));
                    menu1.setApplicationId(pageInfo.getApplicationId());
                    menu1.setType("F");
                    menu1.setComponent("#");
                    menu1.setNoCache("0");
                    menu1.setSort(i);
                    menu1.setStatus("0");
                    if (menu1.getId() == null) {
                        menuService.save(menu1);
                    } else {
                        menuService.updateById(menu1);
                    }
                }
            }
        } else {
            menu.setName(pageInfo.getName());
            menuService.updateById(menu);
        }
    }
}
